﻿using System;
using System.Collections.Generic;
using System.Data;

namespace DataProvider
{
    public sealed class DBManager : IDBManager, IDisposable
    {
        private readonly string _strPrefixParameter = "@";

        public DBManager(DataProvider providerType)
        {
            ProviderType = providerType;

            Parameters = null;
            Transaction = null;
            switch (ProviderType)
            {
                case DataProvider.MySql:
                    _strPrefixParameter = "?";
                    break;
                case DataProvider.SqlServer:
                    _strPrefixParameter = "@";
                    break;
                case DataProvider.Oracle:
                    _strPrefixParameter = ":";
                    break;
                default: _strPrefixParameter = "@"; break;
            }
        }

        public DBManager(DataProvider providerType, string connectionString)
        {
            Parameters = null;
            Transaction = null;
            ProviderType = providerType;
            ConnectionString = connectionString;
        }

        public IDbConnection Connection { get; private set; }
        public IDataReader DataReader { get; set; }
        public DataProvider ProviderType { get; set; }
        public string ConnectionString { get; set; }
        public IDbCommand Command { get; private set; }
        public IDbTransaction Transaction { get; private set; }
        public IDbDataParameter[] Parameters { get; private set; }

        public void Open()
        {
            if (Connection == null)
            {
                Connection = DBManagerFactory.GetConnection(ProviderType);
                Connection.ConnectionString = ConnectionString;
            }
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }

            Command = DBManagerFactory.GetCommand(ProviderType);
        }

        public void Close()
        {
            if (Transaction == null && Connection.State != ConnectionState.Closed)
                Connection.Close();
        }

        public void Dispose()
        {
            GC.SuppressFinalize(this);
            Close();
            Command = null;
            Transaction = null;
            Connection = null;
        }

        public IDbDataParameter CreateParameter(string parameterName, object parameterValue)
        {
            IDbDataParameter param = DBManagerFactory.GetParameter(ProviderType);
            param.ParameterName = _strPrefixParameter + parameterName;
            param.Value = parameterValue;
            return param;
        }

        public void SetParameters(IDbDataParameter[] lstParameters)
        {
            Parameters = lstParameters;
        }

        public void BeginTransaction()
        {
            Open();//open connection
            Transaction = Connection.BeginTransaction();
            Command.Transaction = Transaction;
        }

        public void CommitTransaction()
        {
            if (Transaction != null && Transaction.Connection != null)
            {
                Transaction.Commit();
            }
            Transaction = null;
            Close();//close connection
        }
        public void RollBackTransaction()
        {
            if (Transaction != null)
            {
                Transaction.Rollback();
            }
            Transaction = null;
            Close();//close connection
        }

        public void CloseReader()
        {
            if (DataReader != null)
                DataReader.Close();
        }

        private void AttachParameters(IDbCommand command, IEnumerable<IDbDataParameter> commandParameters)
        {
            foreach (var idbParameter in commandParameters)
            {
                if ((idbParameter.Direction == ParameterDirection.InputOutput)
                    &&
                    (idbParameter.Value == null))
                {
                    idbParameter.Value = DBNull.Value;
                }
                command.Parameters.Add(idbParameter);
            }
        }

        private void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, IEnumerable<IDbDataParameter> commandParameters, int commandTimeOut)
        {
            command.Connection = connection;
            command.CommandText = commandText;
            command.CommandType = commandType;
            command.CommandTimeout = commandTimeOut;

            if (transaction != null)
            {
                command.Transaction = transaction;
            }

            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            else command.Parameters.Clear();
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText, int commandTimeOut)
        {
            Command = DBManagerFactory.GetCommand(ProviderType);
            PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters, commandTimeOut);
            int returnValue = Command.ExecuteNonQuery();
            Command.Parameters.Clear();
            return returnValue;
        }

        public object ExecuteScalar(CommandType commandType, string commandText, int commandTimeOut)
        {
            Command = DBManagerFactory.GetCommand(ProviderType);
            PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters, commandTimeOut);
            object returnValue = Command.ExecuteScalar();
            Command.Parameters.Clear();
            return returnValue;
        }

        public DataSet ExecuteDataSet(CommandType commandType, string commandText, int commandTimeOut)
        {
            Command = DBManagerFactory.GetCommand(ProviderType);
            PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters, commandTimeOut);
            var dataAdapter = DBManagerFactory.GetDataAdapter(ProviderType);
            dataAdapter.SelectCommand = Command;
            var dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            Command.Parameters.Clear();
            return dataSet;
        }

        public IDataReader ExecuteReader(CommandType commandType, string commandText, int commandTimeOut)
        {
            Command = DBManagerFactory.GetCommand(ProviderType);
            Command.Connection = Connection;
            PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters, commandTimeOut);
            DataReader = Command.ExecuteReader();
            Command.Parameters.Clear();
            return DataReader;
        }
    }
}
